using System;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DataTableProvider
/// </summary>
public partial class SQLServerProviders
{

    SqlConnection sqlConnection;
    SqlCommand sqlCommand;
    SqlDataReader sqlDataReader;
    SqlDataAdapter sqlDataAdapter;

    private string connectionString = "";
    public string ConnectionString
    {
        set { connectionString = value; }
        get { return connectionString; }
    }
    private string errorMessage = "";
    public string ErrorMessage
    {
        set { errorMessage = value; }
        get { return errorMessage; }
    }
    private int errorCode = 0;
    public int ErrorCode
    {
        set { errorCode = value; }
        get { return errorCode; }
    }
    public SQLServerProviders()
    {
        connectionString = "server=(local);" +
            "database=RecruitVietnamDb;" +
            "Integrated Security=SSPI;";
    }
    public SQLServerProviders(string conString)
    {
        connectionString = conString;
    }
    void OpenConnection()
    {
        sqlConnection = new SqlConnection();
        sqlConnection.ConnectionString = connectionString;
        sqlConnection.Open();
    }
    void CloseConnection()
    {
        if (sqlConnection.State != ConnectionState.Open)
        {
            sqlConnection.Close();
        }
        sqlConnection.Dispose();
    }
    void AddParameters(SqlCommand sqlCommand,
    string[] parameterCollection,
    string[] valueCollection)
    {
        for (int i = 0; i < parameterCollection.Length; i++)
        {
            sqlCommand.Parameters.AddWithValue(
            parameterCollection[i], valueCollection[i]);
        }
    }

    void AddParameters(SqlCommand sqlCommand,
    string parameterName,
    string valueofParameter)
    {
        sqlCommand.Parameters.AddWithValue(
            parameterName, valueofParameter);

    }

    public int ExecuteNonQuery(
    string commandText, CommandType commandType)
    {
        errorMessage = "";
        int rows = 0;
        try
        {
            OpenConnection();
            sqlCommand = new SqlCommand();
            sqlCommand.CommandText = commandText;
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType = commandType;
            rows =  sqlCommand.ExecuteNonQuery() ;
            CloseConnection();
        }
        catch (SqlException sql)
        {
            errorCode = sql.Number;
            errorMessage = sql.Message;
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;

        }
        return rows;
    }
    
    public int GetOneValue(
    string commandText, CommandType commandType)
    {
        errorMessage = "";
        int rows = 0;
        try
        {
            OpenConnection();
            sqlCommand = new SqlCommand();
            sqlCommand.CommandText = commandText;
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType = commandType;
            rows = Convert.ToInt32(sqlCommand.ExecuteScalar());
            CloseConnection();
        }
        catch (SqlException sql)
        {
            errorCode = sql.Number;
            errorMessage = sql.Message;
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;

        }
        return rows;
    }
    public int GetOneValue(
    string commandText,
    string[] parameterCollection,
    string[] valueCollection,
    CommandType commandType)
    {
        errorMessage = "";
        int rows = 0;
        try
        {
            OpenConnection();
            sqlCommand = new SqlCommand();
            sqlCommand.CommandText = commandText;
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType = commandType;
            AddParameters(sqlCommand,
            parameterCollection, valueCollection);
            rows = Convert.ToInt32(sqlCommand.ExecuteScalar());
            CloseConnection();
        }
        catch (SqlException sql)
        {
            errorCode = sql.Number;
            errorMessage = sql.Message;
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;

        }
        return rows;
    }
    
}
